/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataAccess;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


/**
 *
 * @author Home
 */
public class BillSellDetails {

    public static boolean insertBillDetail(Entity.Bills bill, Entity.BillSellDetails billdetail) {
        String sql = "insert into BillSellDetails(BillID, ItemID, Quantity) values(?, ?, ?)";
        PreparedStatement pst;
        try {
            pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);
            pst.setInt(1, bill.getBillID());
            pst.setInt(2, billdetail.getItemID());
            pst.setInt(3, billdetail.getQuantity());
            if (pst.executeUpdate() == 0) {
                return false;
            }
            exec("update Items set QuantityAvailable=QuantityAvailable-" + billdetail.getQuantity() + " where ItemID='" + billdetail.getItemID() + "'");
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return true;
    }

    public static boolean UpdateBillDetail(Entity.Bills bill, Entity.BillSellDetails billdetail) {
        String sql = "update BillSellDetails set QuantityReceipt=? where BillID=? and ItemID=?";
        PreparedStatement pst;
        try {
            pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);
            pst.setInt(1, billdetail.getQuantityReceipt());
            pst.setInt(2, billdetail.getBillID());
            pst.setInt(3, billdetail.getItemID());
            
            if (pst.executeUpdate() == 0) {
                return false;
            }
            //exec("update Items set QuantityAvailable=QuantityAvailable-" + billdetail.getQuantity() + " where ItemID='" + billdetail.getItemID() + "'");
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return true;
    }

    public static void exec(String sql) {
        try {
            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement();
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }

    }

    public static ArrayList<Entity.BillSellDetails> getAllByID(int order) {
        ArrayList<Entity.BillSellDetails> details = new ArrayList<Entity.BillSellDetails>();
        try {
            PreparedStatement pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall("select * from BillSellDetails where BillID=?");
            pst.setInt(1, order);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                Entity.BillSellDetails bd = new Entity.BillSellDetails();
                bd.setBillID(rs.getInt("BillID"));
                bd.setItemID(rs.getInt("ItemID"));
                bd.setQuantity(rs.getInt("Quantity"));
                bd.setQuantityReceipt(rs.getInt("QuantityReceipt"));
                details.add(bd);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return details;
    }

    public static ArrayList<Entity.BillSellDetails> getAll() {
        ArrayList<Entity.BillSellDetails> details = new ArrayList<Entity.BillSellDetails>();
        try {
            PreparedStatement pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall("select * from BillSellDetails");
            //pst.setInt(1, order);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                Entity.BillSellDetails bd = new Entity.BillSellDetails();
                bd.setBillID(rs.getInt("BillID"));
                bd.setItemID(rs.getInt("ItemID"));
                bd.setQuantity(rs.getInt("Quantity"));
                bd.setQuantityReceipt(rs.getInt("QuantityReceipt"));
                details.add(bd);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return details;
    }
}
